package com.zhxh.nzpos.dao;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.zhxh.nzpos.db.DBHelper;
import com.zhxh.nzpos.model.Sellout;
import com.zhxh.nzpos.model.Statistics;
import com.zhxh.nzpos.utils.RandomStringUtils;

/**
 * Sellout DAO
 * 
 * @author Administrator
 * 
 */
public class SelloutDao {

	DBHelper dbHelper;

	private final static String LOG_TAG = "SelloutDao";
	protected static final String TBL_NAME = "sellout";

	public SelloutDao(Context context) {
		dbHelper = new DBHelper(context);
	}

	/**
	 * add new sellout
	 * 
	 * @param sellout
	 */
	public String save(Sellout sellout) {
		Log.i(LOG_TAG, "SelloutDao  save " + sellout.toString());
		sellout.setId(RandomStringUtils.randomNumeric(10));
		long time = System.currentTimeMillis();
		sellout.setCreated(time);
		sellout.setModified(time);

		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.beginTransaction();// 开始事务
		try {
			db.execSQL(
					"insert into sellout(ID,GoodID,GoodsName,Selldate,Price,Count,Ispayed,OpratorID,created,modified,OrderId) values(?,?,?,?,?,?,?,?,?,?,?)",
					new Object[] { sellout.getId(), sellout.getGoodId(), sellout.getGoodsName(),
							sellout.getSellDate(), sellout.getPrice(),
							sellout.getCount(), sellout.isPayed(),
							sellout.getOpratorId(), sellout.getCreated(),
							sellout.getModified(),null });

			db.setTransactionSuccessful();
		} catch (Exception e) {
			Log.i("SelloutDao  save", e.getMessage());
		} finally {
			db.endTransaction();// 事务结束;
			db.close();
		}
		return sellout.getId();
	}
	
	/**
	 * add  sellouts
	 * 
	 * @param sellouts
	 */
	public boolean saveAll(ArrayList<Sellout> sellouts) {
		Log.i(LOG_TAG, "SelloutDao  save All ");
		
		SQLiteDatabase db = dbHelper.getWritableDatabase();
//		db.beginTransaction();// 开始事务
		try {
		for (Sellout sellout : sellouts) {
			sellout.setId(RandomStringUtils.randomNumeric(10));
			long time = System.currentTimeMillis();
			sellout.setCreated(time);
			sellout.setModified(time);
			
				db.execSQL(
						"insert into sellout(ID,GoodID,GoodsName,Selldate,Price,Count,Ispayed,OpratorID,created,modified) values(?,?,?,?,?,?,?,?,?,?)",
						new Object[] { sellout.getId(), sellout.getGoodId(), sellout.getGoodsName(),
								sellout.getSellDate(), sellout.getPrice(),
								sellout.getCount(), sellout.isPayed(),
								sellout.getOpratorId(), sellout.getCreated(),
								sellout.getModified() });
				
//				db.setTransactionSuccessful();
		}
		} catch (Exception e) {
			Log.i("SelloutDao  save", e.getMessage());
			return false;
		} finally {
			db.close();
		}
//		db.endTransaction();// 事务结束;
		return true;
	}

	public Long save() {
		Long id = 0l ;
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.beginTransaction();// 开始事务
		try {
			ContentValues cv = new ContentValues();
			//cv.put("ID", goods.getId());
			cv.put("OrderNumber",  "");
			cv.put("TotalCount", 0f);
			cv.put("HasPayed", 0f);
			
		 id = 	db.insert("buyin_order", null, cv);
		 Log.i("SelloutDao  save  id", id+"");
			db.setTransactionSuccessful();
		} catch (Exception e) {
			Log.i("SelloutDao  save", e.getMessage());
		}
		db.endTransaction();// 事务结束;
		dbHelper.close();
		return id;
	}
	public String getOrderId() {
		Long id = save();
		
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		
		System.out.println("###########=id="+id);
		 Log.i("SelloutDao  save2222  id", id+"");
		String oderId = "";
		String identi = String.valueOf(id);
		for(int i = 0 ; i < 9 -identi.length(); i++) {
			oderId += "0";
		}
		oderId += identi;
		
		ContentValues cv = new ContentValues();
		//cv.put("ID", goods.getId());
		//cv.put("OrderNumber",  order.getOrderNumber());
		cv.put("OrderNumber",oderId);
		db.update("buyin_order", cv, " ID= '"+id+"'", null);
		dbHelper.close();
		return oderId;
		
	}
	
	public String sellAll(ArrayList<Sellout> sellouts, int totalPoints) {
		String oderid = getOrderId();
		System.out.println("sellAll/...........................");
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		long modified = System.currentTimeMillis();
		try {
			db.beginTransaction();// 开始事务
			
			System.out.println("###########=oderid="+oderid);
			for (Sellout sellout : sellouts) {
				sellout.setId(oderid);
				long time = System.currentTimeMillis();
				sellout.setCreated(time);
				sellout.setModified(time);
				db.execSQL(
						"insert into sellout(ID,GoodID,GoodsName,Selldate,Price,Count,Ispayed,OpratorID,created,modified,OrderId) values(?,?,?,?,?,?,?,?,?,?,?)",
						new Object[] { sellout.getId(), sellout.getGoodId(),
								sellout.getGoodsName(), sellout.getSellDate(),
								sellout.getPrice(), sellout.getCount(),
								sellout.isPayed(), sellout.getOpratorId(),
								sellout.getCreated(), modified,oderid });
				
				
			}
			//TODO更新积分
			db.setTransactionSuccessful();
			db.endTransaction();// 事务结束;
		} catch (Exception e) {
			Log.e("SelloutDao  sell all", e.getMessage());
			oderid = null;
		} finally {
			db.close();
		}
		
		return oderid;
	}
	
	public boolean returnGoods(String orderId) {
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		ArrayList<Sellout> sellouts = new ArrayList<Sellout>();
		
		try {
			db.beginTransaction();// 开始事务
			Cursor cursor = db
					.rawQuery(
							"select ID,GoodID,Count,Selldate from sellout where OrderId=?",
							new String[] { orderId });
			while (cursor.moveToNext()) {
				Sellout sellout = new Sellout();
				sellout.setId(cursor.getString(cursor.getColumnIndex("ID")));
				sellout.setGoodId(cursor.getString(1));
				sellout.setCount(cursor.getInt(2));
				sellout.setSellDate(cursor.getString(3));
				sellouts.add(sellout);
			}
			cursor.close();
			
			if (sellouts.size() > 0) {
				db.execSQL("delete from sellout where OrderId=?", new Object[] { orderId });
				for (Sellout sellout : sellouts) {
					Cursor cursor2 = db
							.rawQuery(
									"select ID,Count from stock where GoodID=? order by Stockdate desc",
									new String[] { sellout.getGoodId() });
					if (cursor2.moveToNext()) {
						String id = cursor2.getString(cursor2.getColumnIndex("ID"));
						int count = cursor2.getInt(1);
						cursor2.close();
						db.execSQL("update stock set Count=? where ID=?", new Object[] { count+sellout.getCount(), id });
					}
				}
			}
			db.setTransactionSuccessful();
			db.endTransaction();// 事务结束;
		} catch (Exception e) {
			Log.e("SelloutDao  return goods", e.getMessage());
			return false;
		} finally {
			db.close();
		}
		return true;
	}
	
	/**
	 * update a sellout
	 * 
	 * @param sellout
	 */

	public void update(Sellout sellout) {
		Log.i(LOG_TAG, "SelloutDao  update " + sellout.toString());
		sellout.setModified(System.currentTimeMillis());
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.beginTransaction();// 开始事务
		try {
			db.execSQL(
					"update sellout set GoodID=?,GoodsName=?,Selldate=?,Price=?,Count=?,Ispayed=?,OpratorID=?,modified=? where ID=?",
					new Object[] { sellout.getGoodId(), sellout.getGoodsName(),sellout.getSellDate(),
							sellout.getPrice(), sellout.getCount(),
							sellout.isPayed(), sellout.getOpratorId(),
							sellout.getModified(), sellout.getId() });

			// db.setTransactionSuccessful();
		} catch (Exception e) {
			Log.i("SelloutDao  update", e.getMessage());
		}
		finally{
			db.endTransaction();// 事务结束;
			db.close();
		}
	}

	/**
	 * get a sellout by id
	 * 
	 * @param i
	 * @return
	 */
	public Sellout find(String id) {
		Log.i(LOG_TAG, "SelloutDao  find " + id);
		Sellout sellout = null;
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db
				.rawQuery(
						"select ID,GoodID,GoodsName,Selldate,Price,Count,Ispayed,OpratorID,created,modified from sellout where ID=?",
						new String[] { id });
		if (cursor.moveToNext()) {
			sellout = new Sellout();

			sellout.setId(cursor.getString(cursor.getColumnIndex("ID")));
			sellout.setGoodId(cursor.getString(1));
			sellout.setGoodsName(cursor.getString(2));
			sellout.setSellDate(cursor.getString(3));
			sellout.setPrice(cursor.getFloat(4));
			sellout.setCount(cursor.getInt(5));
			sellout.setPayed(Boolean.getBoolean(cursor.getString(6)));
			sellout.setOpratorId(cursor.getString(7));
			sellout.setCreated(cursor.getLong(8));
			sellout.setModified(cursor.getLong(9));
			
		}
		cursor.close();
		db.close();
		return sellout;
	}

	/**
	 * delete a sellout by id
	 * 
	 * @param id
	 */
	public void delete(String id) {
		Log.i(LOG_TAG, "SelloutDao  delete " + id);
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.execSQL("delete from sellout where ID=?", new Object[] { id });
		db.close();
	}

	/**
	 * delete all
	 * 
	 * @param id
	 */
	public void deleteAll() {
		Log.i(LOG_TAG, "  delete all from  sellout: ");
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.execSQL("delete from sellout");
		db.close();
	}

	/**
	 * fetch sellouts
	 * 
	 * @param pageNumber
	 * @param pageSize
	 * @return
	 */
	public List<Sellout> getScrollData(int pageNumber, int pageSize) {

		Log.i(LOG_TAG, "SelloutDao  getScrollData " + pageNumber + ","
				+ pageSize);

		List<Sellout> sellouts = new ArrayList<Sellout>();
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db
				.rawQuery(
						"select ID,GoodID,GoodsName,Selldate,Price,Count,Ispayed,OpratorID,created,modified from sellout order by modified desc limit ?,?",
						new String[] {
								String.valueOf((pageNumber - 1) * pageSize),
								String.valueOf(pageSize) });// pageNumber页码
		while (cursor.moveToNext()) {// pageSize每页获取的记录数
			Sellout sellout = new Sellout();
			sellout.setId(cursor.getString(cursor.getColumnIndex("ID")));
			sellout.setGoodId(cursor.getString(1));
			sellout.setGoodsName(cursor.getString(2));
			sellout.setSellDate(cursor.getString(3));
			sellout.setPrice(cursor.getFloat(4));
			sellout.setCount(cursor.getInt(5));
			sellout.setPayed(Boolean.getBoolean(cursor.getString(6)));
			sellout.setOpratorId(cursor.getString(7));
			sellout.setCreated(cursor.getLong(8));
			sellout.setModified(cursor.getLong(9));
			sellouts.add(sellout);
		}
		cursor.close();
		db.close();
		return sellouts;
	}

	
	/**
	 * fetch all sellouts
	 * 
	 * @param pageNumber
	 * @param pageSize
	 * @return
	 */
	public List<Sellout> getAll() {

		Log.i(LOG_TAG, "SelloutDao  getScrollData ");

		List<Sellout> sellouts = new ArrayList<Sellout>();
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db
				.rawQuery(
						"select ID,GoodID,GoodsName,Selldate,Price,Count,Ispayed,OpratorID,created,modified from sellout order by modified desc",
						null);// pageNumber页码
		while (cursor.moveToNext()) {// pageSize每页获取的记录数
			Sellout sellout = new Sellout();
			sellout.setId(cursor.getString(cursor.getColumnIndex("ID")));
			sellout.setGoodId(cursor.getString(1));
			sellout.setGoodsName(cursor.getString(2));
			sellout.setSellDate(cursor.getString(3));
			sellout.setPrice(cursor.getFloat(4));
			sellout.setCount(cursor.getInt(5));
			sellout.setPayed(Boolean.getBoolean(cursor.getString(6)));
			sellout.setOpratorId(cursor.getString(7));
			sellout.setCreated(cursor.getLong(8));
			sellout.setModified(cursor.getLong(9));
			sellouts.add(sellout);
		}
		cursor.close();
		db.close();
		return sellouts;
	}

	/**
	 * get sellout's count
	 * 
	 * @return
	 */
	public long getCount() {
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		long count = DatabaseUtils.queryNumEntries(db, TBL_NAME);
		dbHelper.close();
		return count;
	}
	
	public int getCount(String cateId, String identifier, long btime, long etime) {
		Log.i(LOG_TAG, "SelloutDao  getScrollData " + cateId + "," + identifier);

		int count = 0;
		String sql = "select  count(sellout.ID) from sellout,goods where sellout.GoodID=goods.ID ";
		if (identifier.length() != 0) {
			sql += " and sellout.OrderId='" + identifier+"'";
		} else {
			if (cateId != null) {
				sql += " and goods.CategoryID='" + cateId+"'";
			}
			sql += " and sellout.created >=" + btime + " and sellout.created <=" + etime;
		}

		Log.i("sql= ", sql);
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db.rawQuery(sql, null);// pageNumber页码
        if(cursor.moveToNext()){
        	count = cursor.getInt(0);
        }
		
		cursor.close();
		db.close();
		return count;

	}

	/**
	 * fetch Sellout
	 * 
	 * @param pageNumber
	 * @param pageSize
	 * @return
	 */
	public ArrayList<Sellout> getScrollData(String cateId, String identifier,
			long btime, long etime, int pageNumber, int pageSize) {

		Log.i(LOG_TAG, "SelloutDao  getScrollData " + pageNumber + "," + pageSize);

		String sql = "select sellout.ID,sellout.GoodID,goods.Name,sellout.Selldate,sellout.Price,sellout.Count,sellout.Ispayed,sellout.OpratorID,sellout.created,sellout.modified,sellout.OrderId,goods.Specification from sellout,goods where sellout.GoodID=goods.ID ";
		if (identifier.length() != 0) {
			sql += " and sellout.OrderId='" + identifier+"'";
		} 
		
			if (cateId != null) {
				sql += " and goods.CategoryID='" + cateId+"'";
			}
			sql += " and sellout.created >=" + btime + " and sellout.created <=" + etime;
		

		sql += "  order by sellout.modified desc limit ?,?";

		Log.i("sql= ", sql);
		ArrayList<Sellout> sellouts = new ArrayList<Sellout>();
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db.rawQuery(sql,
				new String[] { String.valueOf((pageNumber - 1) * pageSize),
						String.valueOf(pageSize) });// pageNumber页码
		while (cursor.moveToNext()) {// pageSize每页获取的记录数
			Sellout sellout = new Sellout();
			sellout.setId(cursor.getString(cursor.getColumnIndex("ID")));
			sellout.setGoodId(cursor.getString(1));
			sellout.setGoodsName(cursor.getString(2));
			sellout.setSellDate(cursor.getString(3));
			sellout.setPrice(cursor.getFloat(4));
			sellout.setCount(cursor.getInt(5));
			sellout.setPayed(Boolean.getBoolean(cursor.getString(6)));
			sellout.setOpratorId(cursor.getString(7));
			sellout.setCreated(cursor.getLong(8));
			sellout.setModified(cursor.getLong(9));
			sellout.setOrderId(cursor.getString(10));
			sellout.setSpecification(cursor.getString(11));
			sellouts.add(sellout);
		}
		cursor.close();
		db.close();
		return sellouts;
	}

	/**
	 * fetch Sellout
	 * 
	 * @param pageNumber
	 * @param pageSize
	 * @return
	 */
	public ArrayList<Statistics> getAllSellOut(String cateId,
			long btime, long etime) {

		Log.i(LOG_TAG, "SelloutDao  getScrollData " + btime + "," + etime);

		String sql = "select sum(price *count),category.category,goods.CategoryID from sellout,goods,category where goods.ID = sellout.GoodID and category.ID=goods.CategoryID  ";

		sql += " and sellout.created >=" + btime + " and sellout.created <=" + etime;
		sql += " and category.pid='"+cateId+"'";
		sql += "  group by goods.CategoryID";

		Log.i("sql= ", sql);
		ArrayList<Statistics> sellouts = new ArrayList<Statistics>();
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db.rawQuery(sql,
				null);// pageNumber页码
		while (cursor.moveToNext()) {// pageSize每页获取的记录数
			Statistics statistics = new Statistics();
			statistics.setPrice(cursor.getString(0));
			statistics.setName(cursor.getString(1));
			statistics.setId(cursor.getString(2));
			statistics.setLevel("1");
			sellouts.add(statistics);
		}
		cursor.close();
		db.close();
		return sellouts;
	}

	/**
	 * fetch Sellout
	 * 
	 * @param pageNumber
	 * @param pageSize
	 * @return
	 */
	public ArrayList<Sellout> getScrollData(String cateId,
			long btime, long etime) {

		Log.i(LOG_TAG, "SelloutDao  getScrollData " );

		String sql = "select sellout.ID,sellout.GoodID,goods.Name,sellout.Selldate,sellout.Price,sellout.Count,sellout.Ispayed,sellout.OpratorID,sellout.created,sellout.modified,sellout.OrderId,goods.Specification from sellout,goods where sellout.GoodID=goods.ID ";
		
			if (cateId != null) {
				sql += " and goods.CategoryID=" + cateId;
			}
			sql += " and sellout.created >=" + btime + " and sellout.created <=" + etime;
		

	//	sql += "  order by sellout.modified desc limit ?,?";

		Log.i("sql= ", sql);
		ArrayList<Sellout> sellouts = new ArrayList<Sellout>();
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db.rawQuery(sql,
				null);// pageNumber页码
		while (cursor.moveToNext()) {// pageSize每页获取的记录数
			Sellout sellout = new Sellout();
			sellout.setId(cursor.getString(cursor.getColumnIndex("ID")));
			sellout.setGoodId(cursor.getString(1));
			sellout.setGoodsName(cursor.getString(2));
			sellout.setSellDate(cursor.getString(3));
			sellout.setPrice(cursor.getFloat(4));
			sellout.setCount(cursor.getInt(5));
			sellout.setPayed(Boolean.getBoolean(cursor.getString(6)));
			sellout.setOpratorId(cursor.getString(7));
			sellout.setCreated(cursor.getLong(8));
			sellout.setModified(cursor.getLong(9));
			sellout.setOrderId(cursor.getString(10));
			sellout.setSpecification(cursor.getString(11));
			sellouts.add(sellout);
		}
		cursor.close();
		db.close();
		return sellouts;
	}

}
